"""
    Author:
    Date: 2019-12-10

    照片管理类
"""

from apps.gma import helpers as gma_helpers
from entities.consts import *
from utils import db

TABLE_NAME = 'cmf_users_show_pictures'


def get_photo_count_by_auth_status(status, request=None):
    """ 根据审核状态获取照片数量 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      COUNT(*) AS photo_count
        FROM        {TABLE_NAME} AS p
        JOIN        cmf_users AS u ON u.id=p.uid
        WHERE       {apps_condition} AND
                    p.auth_status={status};
    '''
    data = db.query_data_from_db(sql)
    return data[0]['photo_count'] if data else 0


def get_photo_list_by_auth_status(status, count, request=None):
    """ 根据审核状态获取照片列表 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      p.*,
                    u.avatar,
                    u.nickname
        FROM        {TABLE_NAME} AS p
        JOIN        cmf_users AS u ON p.uid=u.id
        WHERE       {apps_condition} AND
                    p.auth_status={status}
        ORDER BY    p.id DESC
        LIMIT       {count};
    '''
    data = db.query_data_from_db(sql)
    return data if data else []


def get_photo_info(pid):
    """ 根据id查找照片信息 """
    sql = f'''
        SELECT      *
        FROM        {TABLE_NAME}
        WHERE       id={pid}
        LIMIT       1;
    '''
    data = db.query_data_from_db(sql)
    return data[0] if data else None


def update_photo_auth_status(vid, auth_status):
    """ 更新照片的认证状态 """
    sql = f'''
        UPDATE      {TABLE_NAME}
        SET         auth_status={auth_status}
        WHERE       id={vid}
        LIMIT       1;
    '''
    affected_rows = db.execute_sql(sql)
    return affected_rows == 1


def get_user_authing_photo_by(uid):
    """ 根据uid查找待审核的照片信息 """
    sql = f'''
        SELECT      *
        FROM        {TABLE_NAME}
        WHERE       uid={uid} AND
                    auth_status={AUTH_STATUS_WAITING}
        LIMIT       1;
    '''
    data = db.query_data_from_db(sql)
    return data[0] if data else None


def get_user_show_pictures(uid, request=None):
    """ 获取用户审核通过的形象照列表 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      p.show_picture_url
        FROM        {TABLE_NAME} AS p
        JOIN        cmf_users AS u ON p.uid=u.id
        WHERE       {apps_condition} AND
                    p.uid={uid} AND
                    p.auth_status={AUTH_STATUS_CERTIFIED}
        ORDER BY    p.id DESC;
    '''
    data = db.query_data_from_db(sql)
    return [x['show_picture_url'] for x in data] if data else []


if __name__ == '__main__':
    print('ok')
